using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SelectManyModule_File(SqlGuid SessionGuid, SqlGuid ApplicationGuid)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            try
            {
                connection.Open();

                string selectManyModuleFileCommandText =
                @"
                    SELECT 
                        MF.FileGuid, MF.FileId, MF.ApplicationGuid, MA.ApplicationName, MF.[FileName], MF.FileDescription, 
                        MF.FileUrl, MF.FilePath,
                        (CAST(MF.FileVersionMajor AS NVARCHAR) + '.' + CAST(MF.FileVersionMinor  AS NVARCHAR) + '.' + 
                        CAST(MF.FileVersionBuild  AS NVARCHAR) + '.' + CAST(MF.FileVersionRevision AS NVARCHAR))
                        AS FileVersion,
                        MF.FileTypeId, MF.FileOrder

                    FROM 
                        Module_File MF

                    INNER JOIN 
                        Module_Application MA ON MA.ApplicationGuid = MF.ApplicationGuid

                    WHERE 
                        MF.ApplicationGuid = 
                        (
                            SELECT
                                ApplicationGuid

                            FROM 
                                Security_Session SS

                            WHERE 
                                SessionGuid = @SessionGuid
                        )

                    AND
                        MF.ApplicationGuid IN 
                        (
                            SELECT
                                SAR.ApplicationGuid

                            FROM 
                                Security_Session SS

                            INNER JOIN
                                Security_UserRole SUR
                                ON SS.UserGuid = SUR.UserGuid

                            INNER JOIN
                                Security_ApplicationRole SAR
                                ON SUR.RoleGuid = SAR.RoleGuid

                            WHERE 
                                SessionGuid = @SessionGuid
                        )

                    ORDER BY 
                        FileOrder ASC
                ";

                if (!ApplicationGuid.IsNull)
                {
                    selectManyModuleFileCommandText =
                    @"
                        SELECT 
                            MF.FileGuid, MF.FileId, MF.ApplicationGuid, MA.ApplicationName, MF.[FileName], MF.FileDescription, 
                            MF.FileUrl, MF.FilePath,
                            (CAST(MF.FileVersionMajor AS NVARCHAR) + '.' + CAST(MF.FileVersionMinor  AS NVARCHAR) + '.' + 
                            CAST(MF.FileVersionBuild  AS NVARCHAR) + '.' + CAST(MF.FileVersionRevision AS NVARCHAR))
                            AS FileVersion,
                            MF.FileTypeId, MF.FileOrder

                        FROM 
                            Module_File MF

                        INNER JOIN 
                            Module_Application MA ON MA.ApplicationGuid = MF.ApplicationGuid

                        WHERE 
                            MF.ApplicationGuid = @ApplicationGuid

                        ORDER BY 
                            MF.FileOrder ASC
                    ";
                }

                SqlCommand selectManyModuleFileCommand = new SqlCommand(selectManyModuleFileCommandText, connection);

                if (ApplicationGuid.IsNull)
                {
                    selectManyModuleFileCommand.Parameters.Add(new SqlParameter("@SessionGuid", SessionGuid));
                }
                else
                {
                    selectManyModuleFileCommand.Parameters.Add(new SqlParameter("@ApplicationGuid", ApplicationGuid));
                }

                SqlContext.Pipe.ExecuteAndSend(selectManyModuleFileCommand);
            }
            catch (System.Exception exception)
            {
                SqlContext.Pipe.Send("An exception occured");
                SqlContext.Pipe.Send("Message: " + exception.Message);
                SqlContext.Pipe.Send("Source: " + exception.Source);
                SqlContext.Pipe.Send("Stack: " + exception.StackTrace);
                SqlContext.Pipe.Send("Target: " + exception.TargetSite);
            }
            finally
            {
                connection.Close();
            }
        }
    }
};
